const { v4: uuidv4 } = require('uuid');
const XLSX = require('xlsx');
const path = require('path');
const fs = require('fs');
const moment = require('moment');
const { db } = require('../config/database');
const { renderMarkdownTable } = require('../utils/fileUtils');
const { uploadsDir } = require('../middleware/upload');

/**
 * 获取特定版本的数据
 */
const getVersionData = (req, res) => {
    const { versionId } = req.params;
    const { columns } = req.query;
    
    // 获取版本信息
    db.get(
        `SELECT fv.*, f.original_name
         FROM file_versions fv
         JOIN files f ON fv.file_id = f.id
         WHERE fv.id = ?`,
        [versionId],
        (err, versionInfo) => {
            if (err) {
                console.error(err);
                return res.status(500).json({ error: '获取版本信息失败' });
            }
            
            if (!versionInfo) {
                return res.status(404).json({ error: '版本不存在' });
            }

            // 首先获取所有列名，按照第一行的顺序排序
            db.all(
                `SELECT DISTINCT column_name 
                 FROM table_data 
                 WHERE version_id = ? AND row_index = 0
                 ORDER BY rowid`,
                [versionId],
                (err, headerRows) => {
                    if (err) {
                        console.error(err);
                        return res.status(500).json({ error: '获取列信息失败' });
                    }

                    // 如果第一行没有数据，则获取所有列名并尝试按字母顺序排序
                    if (headerRows.length === 0) {
                        db.all(
                            `SELECT DISTINCT column_name 
                             FROM table_data 
                             WHERE version_id = ?
                             ORDER BY column_name`,
                            [versionId],
                            (err, allHeaderRows) => {
                                if (err) {
                                    console.error(err);
                                    return res.status(500).json({ error: '获取列信息失败' });
                                }
                                processData(allHeaderRows.map(row => row.column_name));
                            }
                        );
                    } else {
                        processData(headerRows.map(row => row.column_name));
                    }

                    function processData(orderedHeaders) {
                        // 获取表格数据
                        let query = `
                            SELECT row_index, column_name, cell_value, cell_type
                            FROM table_data 
                            WHERE version_id = ?
                        `;
                        
                        if (columns) {
                            const columnList = columns.split(',').map(col => `'${col}'`).join(',');
                            query += ` AND column_name IN (${columnList})`;
                        }
                        
                        query += ' ORDER BY row_index, column_name';

                        db.all(query, [versionId], (err, rows) => {
                            if (err) {
                                console.error(err);
                                return res.status(500).json({ error: '获取数据失败' });
                            }

                            // 重组数据
                            const dataMap = {};
                            
                            rows.forEach(row => {
                                if (!dataMap[row.row_index]) {
                                    dataMap[row.row_index] = { _rowIndex: row.row_index };
                                }
                                
                                // 处理Markdown内容
                                let cellValue = row.cell_value;
                                if (row.cell_type === 'text' && cellValue) {
                                    cellValue = renderMarkdownTable(cellValue);
                                }
                                
                                dataMap[row.row_index][row.column_name] = {
                                    value: cellValue,
                                    type: row.cell_type
                                };
                            });

                            const data = Object.values(dataMap);
                            
                            // 获取批注信息
                            db.all(
                                'SELECT * FROM annotations WHERE version_id = ?',
                                [versionId],
                                (err, annotations) => {
                                    if (err) {
                                        console.error(err);
                                        return res.status(500).json({ error: '获取批注失败' });
                                    }

                                    // 使用有序的headers数组
                                    const finalHeaders = columns ? 
                                        columns.split(',').filter(col => orderedHeaders.includes(col)) : 
                                        orderedHeaders;

                                    res.json({
                                        versionInfo,
                                        headers: finalHeaders,
                                        data,
                                        annotations
                                    });
                                }
                            );
                        });
                    }
                }
            );
        }
    );
};

/**
 * 更新单元格数据
 */
const updateCell = (req, res) => {
    const { versionId } = req.params;
    const { rowIndex, columnName, value, cellType = 'text' } = req.body;

    db.run(
        `UPDATE table_data 
         SET cell_value = ?, cell_type = ?, updated_at = CURRENT_TIMESTAMP
         WHERE version_id = ? AND row_index = ? AND column_name = ?`,
        [value, cellType, versionId, rowIndex, columnName],
        function(err) {
            if (err) {
                console.error(err);
                return res.status(500).json({ error: '更新失败' });
            }
            
            if (this.changes === 0) {
                return res.status(404).json({ error: '单元格不存在' });
            }
            
            res.json({ success: true, message: '更新成功' });
        }
    );
};

/**
 * 添加自定义列
 */
const addColumn = (req, res) => {
    const { versionId } = req.params;
    const { columnName, columnType = 'text' } = req.body;

    const columnId = uuidv4();
    
    db.run(
        `INSERT INTO custom_columns (id, version_id, column_name, column_type)
         VALUES (?, ?, ?, ?)`,
        [columnId, versionId, columnName, columnType],
        function(err) {
            if (err) {
                console.error(err);
                return res.status(500).json({ error: '添加列失败' });
            }

            // 为所有现有行添加新列的空数据
            db.all(
                'SELECT DISTINCT row_index FROM table_data WHERE version_id = ?',
                [versionId],
                (err, rows) => {
                    if (err) {
                        console.error(err);
                        return res.status(500).json({ error: '获取行数据失败' });
                    }

                    const stmt = db.prepare(`
                        INSERT INTO table_data (id, version_id, row_index, column_name, cell_value, cell_type)
                        VALUES (?, ?, ?, ?, ?, ?)
                    `);

                    rows.forEach(row => {
                        const cellId = uuidv4();
                        stmt.run([cellId, versionId, row.row_index, columnName, '', columnType]);
                    });

                    stmt.finalize();

                    res.json({ 
                        success: true,
                        columnId,
                        message: '列添加成功' 
                    });
                }
            );
        }
    );
};

/**
 * 保存当前版本的修改为新版本
 */
const saveAsNewVersion = (req, res) => {
    const { versionId } = req.params;
    const { submitter, changeDescription } = req.body;

    // 首先获取当前版本信息
    db.get(
        `SELECT fv.*, f.id as file_id, f.original_name
         FROM file_versions fv
         JOIN files f ON fv.file_id = f.id
         WHERE fv.id = ?`,
        [versionId],
        (err, currentVersion) => {
            if (err) {
                console.error(err);
                return res.status(500).json({ error: '获取版本信息失败' });
            }

            if (!currentVersion) {
                return res.status(404).json({ error: '版本不存在' });
            }

            // 获取该文件的最新版本号
            db.get(
                'SELECT MAX(version) as max_version FROM file_versions WHERE file_id = ?',
                [currentVersion.file_id],
                (err, row) => {
                    if (err) {
                        console.error(err);
                        return res.status(500).json({ error: '获取版本信息失败' });
                    }

                    const newVersion = (row.max_version || 0) + 1;
                    const newVersionId = uuidv4();

                    // 创建新版本记录
                    db.run(
                        `INSERT INTO file_versions (id, file_id, version, filename, file_path, submitter, change_description, file_size, created_at)
                         VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)`,
                        [newVersionId, currentVersion.file_id, newVersion, currentVersion.filename, currentVersion.file_path, submitter, changeDescription, currentVersion.file_size],
                        function(err) {
                            if (err) {
                                console.error(err);
                                return res.status(500).json({ error: '创建新版本失败' });
                            }

                            // 复制当前版本的所有数据到新版本
                            db.all(
                                'SELECT row_index, column_name, cell_value, cell_type FROM table_data WHERE version_id = ?',
                                [versionId],
                                (err, tableData) => {
                                    if (err) {
                                        console.error(err);
                                        return res.status(500).json({ error: '获取表格数据失败' });
                                    }

                                    // 批量插入新版本的数据
                                    const stmt = db.prepare(`
                                        INSERT INTO table_data (id, version_id, row_index, column_name, cell_value, cell_type)
                                        VALUES (?, ?, ?, ?, ?, ?)
                                    `);

                                    tableData.forEach(row => {
                                        const cellId = uuidv4();
                                        stmt.run([cellId, newVersionId, row.row_index, row.column_name, row.cell_value, row.cell_type]);
                                    });

                                    stmt.finalize();

                                    // 复制批注数据
                                    db.all(
                                        'SELECT row_index, column_name, annotation_type, annotation_data, created_by FROM annotations WHERE version_id = ?',
                                        [versionId],
                                        (err, annotations) => {
                                            if (err) {
                                                console.error(err);
                                                // 即使批注复制失败，也返回成功，因为主要数据已经保存
                                            } else {
                                                const annotationStmt = db.prepare(`
                                                    INSERT INTO annotations (id, version_id, row_index, column_name, annotation_type, annotation_data, created_by, created_at)
                                                    VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                                                `);

                                                annotations.forEach(annotation => {
                                                    const annotationId = uuidv4();
                                                    annotationStmt.run([annotationId, newVersionId, annotation.row_index, annotation.column_name, annotation.annotation_type, annotation.annotation_data, annotation.created_by]);
                                                });

                                                annotationStmt.finalize();
                                            }

                                            // 更新文件的最后修改时间
                                            db.run(
                                                'UPDATE files SET updated_at = CURRENT_TIMESTAMP WHERE id = ?',
                                                [currentVersion.file_id]
                                            );

                                            res.json({
                                                success: true,
                                                versionId: newVersionId,
                                                version: newVersion,
                                                fileId: currentVersion.file_id,
                                                message: `已保存为新版本 v${newVersion}`
                                            });
                                        }
                                    );
                                }
                            );
                        }
                    );
                }
            );
        }
    );
};

/**
 * 版本比较
 */
const compareVersions = (req, res) => {
    const { version1, version2 } = req.params;
    
    // 获取两个版本的数据
    const getVersionData = (versionId) => {
        return new Promise((resolve, reject) => {
            db.all(
                'SELECT row_index, column_name, cell_value, cell_type FROM table_data WHERE version_id = ? ORDER BY row_index, column_name',
                [versionId],
                (err, rows) => {
                    if (err) {
                        reject(err);
                    } else {
                        const dataMap = {};
                        rows.forEach(row => {
                            const key = `${row.row_index}-${row.column_name}`;
                            dataMap[key] = {
                                value: row.cell_value,
                                type: row.cell_type,
                                rowIndex: row.row_index,
                                columnName: row.column_name
                            };
                        });
                        resolve(dataMap);
                    }
                }
            );
        });
    };
    
    Promise.all([getVersionData(version1), getVersionData(version2)])
        .then(([data1, data2]) => {
            const changes = [];
            const allKeys = new Set([...Object.keys(data1), ...Object.keys(data2)]);
            
            allKeys.forEach(key => {
                const cell1 = data1[key];
                const cell2 = data2[key];
                
                if (!cell1 && cell2) {
                    // 新增的单元格
                    changes.push({
                        rowIndex: cell2.rowIndex,
                        columnName: cell2.columnName,
                        changeType: 'added',
                        oldValue: '',
                        newValue: cell2.value,
                        position: key
                    });
                } else if (cell1 && !cell2) {
                    // 删除的单元格
                    changes.push({
                        rowIndex: cell1.rowIndex,
                        columnName: cell1.columnName,
                        changeType: 'deleted',
                        oldValue: cell1.value,
                        newValue: '',
                        position: key
                    });
                } else if (cell1 && cell2 && cell1.value !== cell2.value) {
                    // 修改的单元格
                    changes.push({
                        rowIndex: cell1.rowIndex,
                        columnName: cell1.columnName,
                        changeType: 'modified',
                        oldValue: cell1.value,
                        newValue: cell2.value,
                        position: key
                    });
                }
            });
            
            res.json({
                success: true,
                changes: changes,
                version1,
                version2
            });
        })
        .catch(err => {
            console.error('版本比较失败:', err);
            res.status(500).json({ error: '版本比较失败' });
        });
};

/**
 * 导出Excel
 */
const exportExcel = (req, res) => {
    const { versionId } = req.params;
    
    db.all(
        `SELECT row_index, column_name, cell_value
         FROM table_data 
         WHERE version_id = ?
         ORDER BY row_index, column_name`,
        [versionId],
        (err, rows) => {
            if (err) {
                console.error(err);
                return res.status(500).json({ error: '导出失败' });
            }

            // 重组数据为Excel格式
            const dataMap = {};
            const headers = new Set();
            
            rows.forEach(row => {
                if (!dataMap[row.row_index]) {
                    dataMap[row.row_index] = {};
                }
                dataMap[row.row_index][row.column_name] = row.cell_value;
                headers.add(row.column_name);
            });

            const headerArray = Array.from(headers);
            const excelData = [headerArray];
            
            Object.keys(dataMap).sort((a, b) => parseInt(a) - parseInt(b)).forEach(rowIndex => {
                const row = headerArray.map(header => dataMap[rowIndex][header] || '');
                excelData.push(row);
            });

            // 创建工作簿
            const wb = XLSX.utils.book_new();
            const ws = XLSX.utils.aoa_to_sheet(excelData);
            XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

            // 生成文件
            const exportPath = path.join(uploadsDir, `export-${versionId}-${Date.now()}.xlsx`);
            XLSX.writeFile(wb, exportPath);

            // 设置正确的响应头
            const filename = `export-${moment().format('YYYY-MM-DD-HH-mm-ss')}.xlsx`;
            res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(filename)}`);
            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            
            res.download(exportPath, filename, (err) => {
                if (err) {
                    console.error('下载错误:', err);
                }
                // 删除临时文件
                fs.unlink(exportPath, () => {});
            });
        }
    );
};

module.exports = {
    getVersionData,
    updateCell,
    addColumn,
    saveAsNewVersion,
    compareVersions,
    exportExcel
};